﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public partial class C_RepairMaintainDAL : BaseDAL<C_RepairMaintain>
    {
        public static readonly SqlConnectionStringBuilder ManageBuilder = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);//取管理库的链接字符串
        public static readonly string ManageDatabase = ManageBuilder.InitialCatalog;

        #region 查询整修保养列表  尤浩 2015.12.1
        public object GetRepairMaintainList(int page, int rows, string start, string end, string center, string station, string actualCode, string ambulanceNumber
                                             , string sendConfirmedPerson, string repairPerson, string inspector, string acceptor, string repairType
                                                , E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID)
        {
            List<RepairMaintainInfo> list = new List<RepairMaintainInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct identity(int,1,1) as 行号,编码=crm.ID,车牌号码=crm.ActualCode,车辆编号=crm.AmbulanceNumber,车辆编码=crm.AmbulanceCode,
                        修理类别=crm.RepairType,是否更换路码=crm.IsChangeRoadCode,进厂里程表数=crm.InMileage,出厂里程表数=crm.OutMileage,进厂油量=crm.InOilMass,
                        出厂油量=crm.OutOilMass,有无备胎=crm.IsSpareWheel,有无行驶证=crm.IsDrivingLicense,送保时间=crm.SendConfirmedTime,
                        竣工时间=crm.CompletionTime,填写时间=crm.FillTime,填写人=crm.FillPerson,备注=crm.Remark,分中心=tc.名称,分站=ts.名称,   
                        整修保养项目=crm.RepairContent,送保人=crm.SendConfirmedPerson,承修人=crm.RepairPerson,车间检验员=crm.Inspector,分站管理科验收人=crm.Acceptor                                         
                        into #tempa                   
                        from C_RepairMaintain crm
                       ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on crm.StationID=ts.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on crm.CenterID=tc.编码 ");
            //sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TPerson tp on crm.StationID=tp.分站编码 ");
            sb.Append(" where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("crm.SendConfirmedTime", Convert.ToDateTime(start), sb);
            WhereClauseUtility.AddDateTimeLessThan("crm.SendConfirmedTime", Convert.ToDateTime(end), sb);           
            WhereClauseUtility.AddStringLike("crm.SendConfirmedPerson", sendConfirmedPerson, sb);
            WhereClauseUtility.AddStringLike("crm.RepairPerson", repairPerson, sb);
            WhereClauseUtility.AddStringLike("crm.Inspector", inspector, sb);
            WhereClauseUtility.AddStringLike("crm.Acceptor", acceptor, sb);
            WhereClauseUtility.AddStringLike("crm.RepairType", repairType, sb);
            if (em == E_StatisticsPermisson.None)
                return null;

            else
            {
                if (em == E_StatisticsPermisson.ALL)
                {
                    WhereClauseUtility.AddStringEqual("crm.CenterID", center, sb);
                    WhereClauseUtility.AddStringEqual("crm.StationID", station, sb);
                    WhereClauseUtility.AddStringEqual("crm.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("crm.AmbulanceNumber", ambulanceNumber, sb);
                }
                else if (em == E_StatisticsPermisson.CENTER)
                {
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("crm.CenterID", selfCenterID, sb);
                    WhereClauseUtility.AddStringEqual("crm.StationID", station, sb);
                    WhereClauseUtility.AddStringEqual("crm.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("crm.AmbulanceNumber", ambulanceNumber, sb);
                }
                else if (em == E_StatisticsPermisson.STATION)
                {
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("crm.CenterID", center, sb);
                    WhereClauseUtility.AddStringEqual("crm.StationID", selfStationID, sb);
                    WhereClauseUtility.AddStringEqual("crm.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("crm.AmbulanceNumber", ambulanceNumber, sb);
                }
                else if (em == E_StatisticsPermisson.SELF)
                {
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("crm.CenterID", center, sb);
                    WhereClauseUtility.AddStringEqual("crm.StationID", station, sb);
                    //WhereClauseUtility.AddStringEqual("tp.工号", selfWorkCode, sb);
                    WhereClauseUtility.AddStringEqual("crm.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("crm.AmbulanceNumber", ambulanceNumber, sb);
                }
                sb.Append(" order by crm.SendConfirmedTime desc ");
                sb.Append(@" select * into #temp from #tempa where 1=1 ");
                sb.Append(@" select top " + rows + " A.*  from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
                sb.Append(@"  SELECT count(*) FROM #temp t   drop table #tempa,#temp ");

                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        RepairMaintainInfo info = new RepairMaintainInfo();
                        info.ID = dr["编码"].ToString();
                        info.Center = dr["分中心"].ToString();
                        info.Station = dr["分站"].ToString();
                        info.ActualCode = dr["车牌号码"].ToString();
                        info.AmbulanceNumber = dr["车辆编号"].ToString();
                        info.AmbulanceCode = dr["车辆编码"].ToString();
                        info.RepairType = dr["修理类别"].ToString();
                        info.IsChangeRoadCode = Convert.ToBoolean(dr["是否更换路码"]);
                        info.InMileage = dr["进厂里程表数"].ToString();
                        info.OutMileage = dr["出厂里程表数"].ToString();
                        info.InOilMass = dr["进厂油量"].ToString();
                        info.OutOilMass = dr["出厂油量"].ToString();
                        info.IsSpareWheel = Convert.ToBoolean(dr["有无备胎"]);
                        info.IsDrivingLicense = Convert.ToBoolean(dr["有无行驶证"]);
                        info.SendConfirmedTime = dr["送保时间"].ToString();
                        info.CompletionTime = dr["竣工时间"].ToString();
                        info.RepairContent = dr["整修保养项目"].ToString();
                        info.SendConfirmedPerson = dr["送保人"].ToString();
                        info.RepairPerson = dr["承修人"].ToString();
                        info.Inspector = dr["车间检验员"].ToString();
                        info.Acceptor = dr["分站管理科验收人"].ToString();
                        info.FillPerson = dr["填写人"].ToString();
                        info.FillTime = dr["填写时间"].ToString();
                        info.Remark = dr["备注"].ToString();
                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
        }
        #endregion     

    }
}
        